package com.mys.service;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.mys.model.IpPojo;

public class GetData {
	
	HttpServletRequest request;

	private static JdbcTemplate jdbcTemplate;
	private DataSource dataSource;
	private int totalPages;

	public int getTotalPages() {
		return totalPages;
	}

	

	public GetData() {

	}

	public GetData(HttpServletRequest request) {

		this.request = request;
	}
	
	@Resource(name = "dataSource")
	public void setDataSource(DataSource dataSource) {
		//this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public List<IpPojo> getAll(){
		String sql = "select * from traceip";
		RowMapper<IpPojo> mapper = new RowMapper<IpPojo>() {

			public IpPojo mapRow(ResultSet rs, int rowNum) throws SQLException {
				IpPojo myip = new IpPojo();
				myip.setId(rs.getInt("id"));
				myip.setIpaddress(rs.getString("ipaddress"));
				myip.setRealaddress(rs.getString("realaddress"));
				myip.setCreatetime(rs.getString("createtime"));
				return myip;
			}
			
		};
		return jdbcTemplate.query(sql, mapper);
		
	}
	
	
	public void add(final String shortUrl,final String longUrl){
		
//		final String shortUrl = shortUrlModel.getShorturl();
//		final String longUrl = shortUrlModel.getLongurl();
		final int id = 1;
		jdbcTemplate.update("insert into dealurl values(?,?,?)",new PreparedStatementSetter(){
			
			@Override
			public void setValues(PreparedStatement ps)  {
				// TODO Auto-generated method stub
				try {
					ps.setInt(1, 0);
					ps.setString(2, shortUrl);
					ps.setString(3, longUrl);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					
					
					System.out.println(e.getSQLState());
				}
			}
			
		});
		
	}
	
	
	
	
	public Pagination getPagination(int page, int rows) {
		String sql = "select * from traceip";
		return new Pagination(sql, page,rows, this.jdbcTemplate);
	}
	
	
	
	
	public List getResultByPage(int page,int rows){
		String sql = "select * from traceip";
		String countSQL = getSQLCount(sql);
		//setPage(page);
		setTotalPages(rows,jdbcTemplate.queryForInt(countSQL));
		int startIndex = (page - 1) * rows;	
		
		StringBuffer paginationSQL = new StringBuffer(" ");
		paginationSQL.append(sql);
		paginationSQL.append(" limit "+ startIndex+","+rows);
		//System.out.print(paginationSQL.toString());
		return jdbcTemplate.queryForList(paginationSQL.toString());
		
		//return null;
		
	}
	
	public String getSQLCount(String sql){
		String sqlBak = sql.toLowerCase();
		String searchValue = " from ";
		String sqlCount = "select count(*) from "+ sql.substring(sqlBak.indexOf(searchValue)+searchValue.length(), sqlBak.length());
		return sqlCount;
	}
	
	public void setTotalPages(int numPerPage,int totalRows) {
		if (totalRows % numPerPage == 0) {
			this.totalPages= totalRows / numPerPage;
		} else {
			this.totalPages = (totalRows / numPerPage) + 1;
		}
	}
	
	
	public IpPojo findById(String id){
		String sql = "select * from traceip where id=?";
		RowMapper<IpPojo> mapper = new RowMapper<IpPojo>() {

			public IpPojo mapRow(ResultSet rs, int rowNum) throws SQLException {
				IpPojo myip = new IpPojo();
				myip.setId(rs.getInt("id"));
				myip.setIpaddress(rs.getString("ipaddress"));
				myip.setRealaddress(rs.getString("realaddress"));
				myip.setCreatetime(rs.getString("createtime"));
				return myip;
			}
			
		};
		
		List<IpPojo> list = jdbcTemplate.query(sql, mapper, new Object[]{id});
		if (list.size() == 0) {
			return null;
		}
		return list.get(0);
	}
	
	public void delId(String id){
		String sql = "delete from traceip where id=?";
		jdbcTemplate.update(sql, new Object[] {id});
	}
	
}
